Imports BusinessLogicLayer
Imports BusinessLogicLayer.BLMain
Imports DataAccessLayer.DLMain
Imports UniversalLayer
Imports UniversalLayer.ULMain
Imports UniversalLayer.ULConverter
Imports System.Xml

'''' <summary>
'''' Modules
'''' </summary>
'''' <remarks></remarks>
'Public Enum Modules
'    Keyword = 0
'    Volume = 1
'    Collection = 2
'    Condition = 3
'End Enum

''' <summary>
''' NO COMMENTS
''' </summary>
''' <remarks></remarks>
Public Class DLReader

    'Dim _BLMain As BLMain
    'Dim _DLConnector As DLConnector
    'Dim _DLMain As DLMain
    Dim con As SqlClient.SqlConnection

    Public Sub New(ByVal sMSG As String)
        Dim _DLConnector As DLConnector
        Try
            _DLConnector = New DLConnector
            _DLConnector.CreateConnection(con, sMSG) 'Open connection
        Finally
            _DLConnector = Nothing
        End Try
    End Sub

    Protected Overrides Sub Finalize()
        DLConnector.CloseConnection(con)
        MyBase.Finalize()
    End Sub

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="sPath"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ParseXML(ByVal sPath As String) As DataSet

        Dim sXmlDataset As New DataSet

        Return sXmlDataset

    End Function

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="sTableName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function FetchNextIdentity(ByVal sTableName As String) As Integer
        Dim dtIdentity As DataTable
        Try
            dtIdentity = New DataTable
            ExecuteSQL("SELECT IDENT_CURRENT('" & sTableName & "') + IDENT_INCR('" & sTableName & "') AS NextIdentity WHERE IDENT_CURRENT('" & sTableName & "') IS NOT NULL", dtIdentity, sTableName & "_Identity")

            If dtIdentity.Rows.Count = 1 Then Return CInt(dtIdentity.Rows(0).Item("NextIdentity")) _
                Else Return Nothing
        Finally
            dtIdentity = Nothing
        End Try
    End Function

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function FetchNextSerial(ByVal iEntertainmentType As eEntertainmentType) As Integer
        Return CInt(ExecuteSQL("SELECT COALESCE(MAX(SerialNo), 0) + 1 AS SerialNo FROM " & iEntertainmentType.ToString.Replace("TV", "") & "DetailsView").Item("SerialNo"))
    End Function

#Region "Execute SQL statements"

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="sSQL"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteSQL(ByVal sSQL As String) As DataRow
        Dim dtResults As New DataTable
        Try
            ExecuteSQL(sSQL, dtResults, "Results")
            Return dtResults.Rows(0)
        Finally
            dtResults = Nothing
        End Try
    End Function

    ''' <summary>
    ''' Executes a SQL query into a DataTable
    ''' </summary>
    ''' <param name="sSQL">SQL query to execute</param>
    ''' <param name="dtTarget">DataTable to be populated</param>
    ''' <param name="sTableName">Name of DataTable</param>
    ''' <remarks></remarks>
    Public Sub ExecuteSQL(ByVal sSQL As String, ByRef dtTarget As DataTable, Optional ByVal sTableName As String = "Target")
        'Dim _DLConnector As New DLConnector
        'Dim con As SqlClient.SqlConnection
        Dim da As SqlClient.SqlDataAdapter
        Dim ds As DataSet

        Try
            'Fetch records from db
            da = New SqlClient.SqlDataAdapter(sSQL, con)
            ds = New DataSet
            da.Fill(ds, sTableName)

            'Populate the DataTable
            If IsEmpty(dtTarget) Then dtTarget = New DataTable
            dtTarget = ds.Tables(sTableName)

        Finally
            '_DLConnector = Nothing
            'con = Nothing
            da = Nothing
            ds = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Fetches DB attribute w.r.t. GUI attribute; used for generating SQL from GUI
    ''' </summary>
    ''' <param name="sCategory">Category</param>
    ''' <returns>Category</returns>
    ''' <remarks></remarks>
    Public Function FormatDBAttribute(ByRef sCategory As String) As String
        Select Case sCategory.ToUpper
            Case "Alternate Title".ToUpper : sCategory = "Alternate Title".Replace(" ", "").ToUpper
            Case "Discs".ToUpper : sCategory = "nDiscs"
            Case "Number".ToUpper : sCategory = "SerialNo"
            Case Else : sCategory = sCategory
        End Select
        Return sCategory
    End Function

#End Region

#Region "Execute DB Objects"

    ''' <summary>
    ''' NOT COMPLETE
    ''' </summary>
    ''' <param name="sViewName"></param>
    ''' <param name="dtTarget"></param>
    ''' <param name="sCondition"></param>
    ''' <remarks></remarks>
    Public Sub ExecuteView(ByVal sViewName As String, ByRef dtTarget As DataTable, Optional ByVal sCondition As String = "")
        '' '' NOT REQUIRED
        ' '' ''Open connection
        '' ''Dim _DLConnector As New DLConnector
        '' ''Dim con As SqlClient.SqlConnection = Nothing
        '' ''_DLConnector.CreateConnection(con)
        ' '' ''
        ' '' ''Close connection
        '' ''_DLConnector.CloseConnection(con)

        'use_CON_directly()
    End Sub

    ''' <summary>
    ''' NOT COMPLETE
    ''' </summary>
    ''' <param name="sStoreProcName"></param>
    ''' <param name="dtTarget"></param>
    ''' <param name="sParam"></param>
    ''' <remarks></remarks>
    Public Sub ExecuteSP(ByVal sStoreProcName As String, ByRef dtTarget As DataTable, ByVal sParam() As String)
        ' '' ''Open connection
        '' ''Dim _DLConnector As New DLConnector
        '' ''Dim con As SqlClient.SqlConnection = Nothing
        '' ''_DLConnector.CreateConnection(con)

        Dim da As New SqlClient.SqlDataAdapter("exec sp_SearchMuvee 'title','cast',''", con)
        Dim dt As New DataTable
        da.Fill(dt)
        'dgDTS.DataSource = Dt

        ' '' ''Close connection
        '' ''_DLConnector.CloseConnection(con)
    End Sub

#End Region

#Region "Read from File"

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="sFullFilePath"></param>
    ''' <param name="dtTableCSV"></param>
    ''' <param name="sTableName"></param>
    ''' <remarks></remarks>
    Public Sub ReadCSV(ByVal sFullFilePath As String, ByRef dtTableCSV As DataTable, Optional ByVal sTableName As String = "CSV")
        ' http://www.codeproject.com/Articles/9258/A-Fast-CSV-Reader

        Dim StreamReadFile As New IO.StreamReader(sFullFilePath)
        Dim dr As DataRow
        dtTableCSV = New DataTable
        dtTableCSV.TableName = sTableName

        Try
            For Each sItem As String In StreamReadFile.ReadLine.Split(","c)
                dtTableCSV.Columns.Add(sItem)
            Next

            Dim iColumn As Integer
            While Not StreamReadFile.EndOfStream
                dr = dtTableCSV.NewRow
                iColumn = 0
                For Each sItem As String In StreamReadFile.ReadLine.Split(","c)
                    dr(dtTableCSV.Columns(iColumn)) = sItem.Replace("`_`", ",")
                    iColumn += 1
                Next
                dtTableCSV.Rows.Add(dr)
                dr = Nothing
                'dtTableCSV.Rows.Add(StreamReadFile.ReadLine.Replace(",", "`").Split(","c))
            End While
        Finally
            StreamReadFile = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="sXMLPath"></param>
    ''' <param name="dtTableXML"></param>
    ''' <remarks></remarks>
    Public Sub ReadXML(ByVal sXMLPath As String, ByRef dtTableXML As DataTable)
        'http://www.codeproject.com/Articles/4826/XML-File-Parsing-in-VB-NET
        Exit Sub

        'If IsEmpty(dtTableXmlSchema) Then dtTableXmlSchema = New DataTable
        'Dim dtTableXml As New DataTable
        '
        'dtTableXmlSchema.ReadXmlSchema("~/DataTableSchema.xml")
        'dtTableXML.ReadXml(sPath)

        'Dim doc As New System.Xml.XmlDocument
        'Dim xml As String = "<?xml version=""1.0"" encoding=""utf-8""?>" _
        '                    & "<pupil>" _
        '                      & "<name>Test Name</name>" _
        '                     & "<tagid>00000000000000000001</tagid>" _
        '                    & "</pupil>"
        ''doc.Load("filename.xml")
        'doc.LoadXml(xml)
        'Dim list As Xml.XmlNodeList = doc.GetElementsByTagName("name")
        'For Each item As System.Xml.XmlElement In list
        '    MsgBox(item.InnerText)
        'Next

        'Read through the XML. Note that this step shows an outer "while" loop,
        'and the next two steps show how to use that loop and read XML.
        'After it is loaded, XmlTextReader performs sequential reads to move across
        'the XML data and uses the Read method to get the next record.
        'The Read method returns false if there are no more records.
        Dim rdrXML As XmlTextReader = New XmlTextReader(sXMLPath)
        'Do While (reader.Read())
        '    ' Do some work here on the data.
        '    MsgBox(reader.Name)
        'Loop

        'Inspect the nodes. To process the XML data, each record has a node type,
        'which can be determined from the NodeType property. The Name
        'and Value properties return the node name (the element and attribute names)
        'and the node value (the node text) of the current node (or record).
        'The NodeType enumeration determines the node type. The following sample code
        'displays the name of the elements and the document type. Note that this sample
        'ignores element attributes.
        Dim x As Text.StringBuilder
        Do While (rdrXML.Read())
            x = New Text.StringBuilder
            Select Case rdrXML.NodeType
                Case XmlNodeType.Element 'Display beginning of element.
                    x.Append("<" & rdrXML.Name & ">")
                Case XmlNodeType.Text 'Display the text in each element.
                    x.Append(rdrXML.Value)
                Case XmlNodeType.EndElement 'Display end of element.
                    x.Append("</" & rdrXML.Name & ">")
            End Select
            MsgBox(x.ToString, , "nodes")
            x = Nothing
        Loop
        Exit Sub

        'Inspect the attributes. Element node types can include a list of attribute
        'nodes that are associated with them. The MovetoNextAttribute method moves
        'sequentially through each attribute in the element. Use the HasAttributes property
        'to test whether the node has any attributes. The AttributeCount property
        'returns the number of attributes for the current node.
        Do While (rdrXML.Read())
            Select Case rdrXML.NodeType
                Case XmlNodeType.Element 'Display beginning of element.
                    Console.Write("<" + rdrXML.Name)
                    If rdrXML.HasAttributes Then 'If attributes exist
                        While rdrXML.MoveToNextAttribute()
                            'Display attribute name and value.
                            Console.Write(" {0}='{1}'", rdrXML.Name, rdrXML.Value)
                        End While
                    End If
                    Console.WriteLine(">")
                Case XmlNodeType.Text 'Display the text in each element.
                    Console.WriteLine(rdrXML.Value)
                Case XmlNodeType.EndElement 'Display end of element.
                    Console.Write("</" + rdrXML.Name)
                    Console.WriteLine(">")
            End Select
        Loop

        Do While (rdrXML.Read())
            Select Case rdrXML.NodeType
                Case XmlNodeType.Element 'Display beginning of element.
                    Console.Write("<" + rdrXML.Name)
                    If rdrXML.HasAttributes Then 'If attributes exist
                        While rdrXML.MoveToNextAttribute()
                            'Display attribute name and value.
                            Console.Write(" {0}='{1}'", rdrXML.Name, rdrXML.Value)
                        End While
                    End If
                    Console.WriteLine(">")
                Case XmlNodeType.Text 'Display the text in each element.
                    Console.WriteLine(rdrXML.Value)
                Case XmlNodeType.EndElement 'Display end of element.
                    Console.Write("</" + rdrXML.Name)
                    Console.WriteLine(">")
            End Select
        Loop
        Console.ReadLine()

    End Sub

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="sXLSPath"></param>
    ''' <param name="dtTableXLS"></param>
    ''' <remarks></remarks>
    Public Sub ReadXLS(ByVal sXLSPath As String, ByRef dtTableXLS As DataTable)
        ' http://www.c-sharpcorner.com/UploadFile/mgold/HowtoOepnandReadanExcelSpreadsheetinaListViewin.NET11282005034134AM/HowtoOepnandReadanExcelSpreadsheetinaListViewin.NET.aspx
    End Sub

#End Region

#Region "Fetch Data by Modules"

    ''' <summary>
    ''' Returns set of keywords w.r.t. entered text and seleted category
    ''' </summary>
    ''' <param name="dtKeywords">DataTable to be populated</param>
    ''' <param name="sCategory">Selected category</param>
    ''' <param name="sKeyword">Entered keyword</param>
    ''' <remarks></remarks>
    Public Sub FetchKeyword(ByRef dtKeywords As DataTable, Optional ByVal sCategory As String = "Title", Optional ByVal sKeyword As String = "")
        Dim sSQL As String
        Dim _DLMain As New DLMain

        Try
            'Create SQL query
            If InStr(_DLMain.sFieldsBinary, sCategory) > 0 And Not IsEmpty(sCategory) Then
                'sSQL = "SELECT DISTINCT " & IIf(Not IsEmpty(sCategory), sCategory, "1").ToString & " FROM MuveeDetailsView WHERE Status<>'d' ORDER BY 1 DESC"
                sSQL = "SELECT DISTINCT CASE " & sCategory & " WHEN '" & ULConverter.ConvertBoolToString(True) & "' THEN '" & ULConverter.ConvertBoolToString(True, eBooleanStyle.FullText) & "' WHEN '" & ULConverter.ConvertBoolToString(False) & "' THEN '" & ULConverter.ConvertBoolToString(False, eBooleanStyle.FullText) & "' END AS " & sCategory & " FROM MuveeDetailsView WHERE Status<>'d' ORDER BY 1 DESC"
            Else
                If Not IsEmpty(sKeyword) Then
                    sSQL = "SELECT DISTINCT " & IIf(Not IsEmpty(sCategory), sCategory, "1").ToString & " AS " & sCategory & " FROM MuveeDetailsView WHERE " & sCategory & " like '" & sKeyword & "%' AND Status<>'d' ORDER BY 1"
                Else
                    sSQL = "SELECT DISTINCT " & IIf(Not IsEmpty(sCategory), sCategory, "1").ToString & " AS " & sCategory & " FROM MuveeDetailsView WHERE (0=1)"
                End If
            End If

            'Populate the DataTable
            ExecuteSQL(sSQL, dtKeywords, "Keyword" & sCategory)

        Finally
            sSQL = Nothing
            _DLMain = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Fetches all People defined in database
    ''' </summary>
    ''' <param name="dtPeople">DataTable to be populated</param>
    ''' <param name="sCondition">Additional Condition (should not start with "and")</param>
    ''' <remarks></remarks>
    Public Sub FetchPeople(ByRef dtPeople As DataTable, Optional ByVal sCondition As String = "")
        Dim sSQL As String
        sCondition = sCondition.Trim
        Dim _DLMain As New DLMain

        Try
            'Create SQL query
            If IsEmpty(sCondition) Then sCondition = "(1=1)"
            CleanSQL(sCondition)
            sSQL = "SELECT PKey, Name FROM PeopleMain WHERE Status<>'d' and " & sCondition & " ORDER BY Name ASC"

            'Populate the DataTable
            ExecuteSQL(sSQL, dtPeople, "People")

        Finally
            sSQL = Nothing
            _DLMain = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Fetches all Volumes defined in database
    ''' </summary>
    ''' <param name="dtVolume">DataTable to be populated</param>
    ''' <param name="sCondition">Additional Condition (should not start with "and")</param>
    ''' <remarks></remarks>
    Public Sub FetchVolumes(ByRef dtVolume As DataTable, Optional ByVal sCondition As String = "")
        Dim sSQL As String
        sCondition = sCondition.Trim
        Dim _DLMain As New DLMain

        Try
            'Create SQL query
            If IsEmpty(sCondition) Then sCondition = "(1=1)"
            CleanSQL(sCondition)
            sSQL = "SELECT PKey, Volume, Description FROM VolumeMain WHERE Status<>'d' and " & sCondition & " ORDER BY Volume ASC"

            'Populate the DataTable
            ExecuteSQL(sSQL, dtVolume, "Volume")

        Finally
            sSQL = Nothing
            _DLMain = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Fetches all Collections defined in database
    ''' </summary>
    ''' <param name="dtCollection">DataTable to be populated</param>
    ''' <param name="sCondition">Additional Condition (should not start with "and")</param>
    ''' <remarks></remarks>
    Public Sub FetchCollections(ByRef dtCollection As DataTable, Optional ByVal sCondition As String = "")
        Dim sSQL As String
        sCondition = sCondition.Trim
        Dim _DLMain As New DLMain

        Try
            'Create SQL query
            If IsEmpty(sCondition) Then sCondition = "(1=1)"
            CleanSQL(sCondition)
            sSQL = "SELECT PKey, Collection, Description FROM CollectionMain WHERE Status<>'d' and " & sCondition & " ORDER BY Collection ASC"

            'Populate the DataTable
            ExecuteSQL(sSQL, dtCollection, "Collection")

        Finally
            sSQL = Nothing
            _DLMain = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Fetches all Conditions defined in database
    ''' </summary>
    ''' <param name="dtCondition">DataTable to be populated</param>
    ''' <param name="sCondition">Additional Condition (should not start with "and")</param>
    ''' <remarks></remarks>
    Public Sub FetchConditions(ByRef dtCondition As DataTable, Optional ByVal sCondition As String = "")
        Dim sSQL As String
        sCondition = sCondition.Trim
        Dim _DLMain As New DLMain

        Try
            'Create SQL query
            If IsEmpty(sCondition) Then sCondition = "(1=1)"
            CleanSQL(sCondition)
            sSQL = "SELECT PKey, Condition, Description FROM CondMain WHERE Status<>'d' and " & sCondition & " ORDER BY Condition ASC"

            'Populate the DataTable
            ExecuteSQL(sSQL, dtCondition, "Condition")

        Finally
            sSQL = Nothing
            _DLMain = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Fetches all Media defined in database
    ''' </summary>
    ''' <param name="dtMedia">DataTable to be populated</param>
    ''' <param name="sCondition">Additional Condition (should not start with "and")</param>
    ''' <remarks></remarks>
    Public Sub FetchMedia(ByRef dtMedia As DataTable, Optional ByVal sCondition As String = "")
        Dim sSQL As String
        sCondition = sCondition.Trim
        Dim _DLMain As New DLMain

        Try
            'Create SQL query
            If IsEmpty(sCondition) Then sCondition = "(1=1)"
            CleanSQL(sCondition)
            sSQL = "SELECT PKey, Medium, Description FROM MediumMain WHERE Status<>'d' and " & sCondition & " ORDER BY Medium ASC"

            'Populate the DataTable
            ExecuteSQL(sSQL, dtMedia, "Medium")

        Finally
            sSQL = Nothing
            _DLMain = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Fetches all DVD Regions defined in database
    ''' </summary>
    ''' <param name="dtRegion">DataTable to be populated</param>
    ''' <param name="sCondition">Additional Condition (should not start with "and")</param>
    ''' <remarks></remarks>
    Public Sub FetchDvdRegions(ByRef dtRegion As DataTable, Optional ByVal sCondition As String = "")
        Dim sSQL As String
        sCondition = sCondition.Trim
        Dim _DLMain As New DLMain

        Try
            'Create SQL query
            If IsEmpty(sCondition) Then sCondition = "(1=1)"
            CleanSQL(sCondition)
            sSQL = "SELECT PKey, Region, Description FROM RegionMain WHERE Status<>'d' and " & sCondition & " ORDER BY Region ASC"

            'Populate the DataTable
            ExecuteSQL(sSQL, dtRegion, "Region")

        Finally
            sSQL = Nothing
            _DLMain = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Fetches all DVD Layers defined in database
    ''' </summary>
    ''' <param name="dtLayer">DataTable to be populated</param>
    ''' <param name="sCondition">Additional Condition (should not start with "and")</param>
    ''' <remarks></remarks>
    Public Sub FetchDvdLayers(ByRef dtLayer As DataTable, Optional ByVal sCondition As String = "")
        Dim sSQL As String
        sCondition = sCondition.Trim
        Dim _DLMain As New DLMain

        Try
            'Create SQL query
            If IsEmpty(sCondition) Then sCondition = "(1=1)"
            CleanSQL(sCondition)
            sSQL = "SELECT PKey, Layer, Description FROM LayerMain WHERE Status<>'d' and " & sCondition & " ORDER BY Layer ASC"

            'Populate the DataTable
            ExecuteSQL(sSQL, dtLayer, "Layer")

        Finally
            sSQL = Nothing
            _DLMain = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Fetches all Colours defined in database
    ''' </summary>
    ''' <param name="dtColour">DataTable to be populated</param>
    ''' <param name="sCondition">Additional Condition (should not start with "and")</param>
    ''' <remarks></remarks>
    Public Sub FetchColours(ByRef dtColour As DataTable, Optional ByVal sCondition As String = "")
        Dim sSQL As String
        sCondition = sCondition.Trim
        Dim _DLMain As New DLMain

        Try
            'Create SQL query
            If IsEmpty(sCondition) Then sCondition = "(1=1)"
            CleanSQL(sCondition)
            sSQL = "SELECT PKey, Colour, Description FROM ColourMain WHERE Status<>'d' and " & sCondition & " ORDER BY Colour ASC"

            'Populate the DataTable
            ExecuteSQL(sSQL, dtColour, "Colour")

        Finally
            sSQL = Nothing
            _DLMain = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Fetches all Video Codecs defined in database
    ''' </summary>
    ''' <param name="dtVCodec">DataTable to be populated</param>
    ''' <param name="sCondition">Additional Condition (should not start with "and")</param>
    ''' <remarks></remarks>
    Public Sub FetchVCodecs(ByRef dtVCodec As DataTable, Optional ByVal sCondition As String = "")
        Dim sSQL As String
        sCondition = sCondition.Trim
        Dim _DLMain As New DLMain

        Try
            'Create SQL query
            If IsEmpty(sCondition) Then sCondition = "(1=1)"
            CleanSQL(sCondition)
            sSQL = "SELECT PKey, VCodec, Description FROM VCodecMain WHERE Status<>'d' and " & sCondition & " ORDER BY VCodec ASC"

            'Populate the DataTable
            ExecuteSQL(sSQL, dtVCodec, "Colour")

        Finally
            sSQL = Nothing
            _DLMain = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Fetches all Audio Codecs defined in database
    ''' </summary>
    ''' <param name="dtACodec">DataTable to be populated</param>
    ''' <param name="sCondition">Additional Condition (should not start with "and")</param>
    ''' <remarks></remarks>
    Public Sub FetchACodecs(ByRef dtACodec As DataTable, Optional ByVal sCondition As String = "")
        Dim sSQL As String
        sCondition = sCondition.Trim
        Dim _DLMain As New DLMain

        Try
            'Create SQL query
            If IsEmpty(sCondition) Then sCondition = "(1=1)"
            CleanSQL(sCondition)
            sSQL = "SELECT PKey, ACodec, Description FROM ACodecMain WHERE Status<>'d' and " & sCondition & " ORDER BY ACodec ASC"

            'Populate the DataTable
            ExecuteSQL(sSQL, dtACodec, "Colour")

        Finally
            sSQL = Nothing
            _DLMain = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Fetches all TV Series Parents (for episodes only) declared in database
    ''' </summary>
    ''' <param name="dtSeriesParent">DataTable to be populated</param>
    ''' <param name="sCondition">Additional Condition (should not start with "and")</param>
    ''' <remarks></remarks>
    Public Sub FetchSeriesParent(ByRef dtSeriesParent As DataTable, Optional ByVal sCondition As String = "")
        Dim sSQL As String
        sCondition = sCondition.Trim
        Dim _DLMain As New DLMain

        Try
            'Create SQL query
            If IsEmpty(sCondition) Then sCondition = "(1=1)"
            CleanSQL(sCondition)
            sSQL = "SELECT PKey, SeriesParentPKey, SeriesID, SeriesTitle FROM SeriesDetailsView WHERE Status<>'d' and " & sCondition & " ORDER BY ACodec ASC"

            'Populate the DataTable
            ExecuteSQL(sSQL, dtSeriesParent, "SeriesParent")

        Finally
            sSQL = Nothing
            _DLMain = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' NO COMMENTS: Fetches Watchlist
    ''' </summary>
    ''' <param name="dtWatchlist"></param>
    ''' <param name="sPreviewPKey"></param>
    ''' <param name="sCondition"></param>
    ''' <param name="sSort"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function FetchWatchlist(ByRef dtWatchlist As DataTable, Optional ByVal sPreviewPKey As String = "", _
    Optional ByVal sCondition As String = "", Optional ByVal sSort As String = "DateAdded DESC") As String
        Dim sSQL As String
        sCondition = sCondition.Trim
        Dim _DLMain As New DLMain

        Try
            'Create SQL query
            If IsEmpty(sCondition) Then sCondition = "(1=1)"
            If IsEmpty(sSort) Then sSort = "DateAdded DESC"
            sSort = " ORDER  BY " & sSort

            CleanSQL(sCondition)
            If Not IsEmpty(sPreviewPKey) Then
                'sSQL = "SELECT em.PKey, /*COALESCE(EntertainmentMainPKey,0) AS EntertainmentMainPKey,*/ COALESCE(DateAdded,'') AS DateAdded, COALESCE(DateWatched,'') AS DateWatched FROM EntertainmentMain em LEFT OUTER JOIN WatchlistMain wm ON wm.EntertainmentMainPKey=em.PKey AND wm.Status<>'d' AND em.Status<>'d' WHERE em.PKey='" & sPreviewPKey & "' AND " & sCondition & " ORDER BY DateAdded DESC"
                sSQL = "SELECT PKey, EntertainmentMainPKey, REPLACE(COALESCE(CONVERT(varchar,WatchlistMain.DateAdded,106),'--'),' ',' ') as DateAdded, " & _
                       "REPLACE(COALESCE(CONVERT(varchar,WatchlistMain.DateWatched,106),'--'),' ','-') as DateWatched FROM WatchlistMain WHERE EntertainmentMainPKey=" & sPreviewPKey & " AND " & sCondition & sSort
            Else
                'sSQL = "SELECT em.PKey, /*COALESCE(EntertainmentMainPKey,0) AS EntertainmentMainPKey,*/ COALESCE(DateAdded,'') AS DateAdded, COALESCE(DateWatched,'') AS DateWatched FROM EntertainmentMain em LEFT OUTER JOIN WatchlistMain wm ON wm.EntertainmentMainPKey=em.PKey AND wm.Status<>'d' AND em.Status<>'d' WHERE " & sCondition & " ORDER BY DateAdded DESC"
                sSQL = "SELECT PKey, EntertainmentMainPKey, REPLACE(COALESCE(CONVERT(varchar,WatchlistMain.DateAdded,106),'--'),' ',' ') as DateAdded, " & _
                       "REPLACE(COALESCE(CONVERT(varchar,WatchlistMain.DateWatched,106),'--'),' ','-') as DateWatched FROM WatchlistMain WHERE " & sCondition & sSort
            End If

            'Populate the DataTable
            ExecuteSQL(sSQL, dtWatchlist, "Watchlist")
            Return sSQL

        Finally
            sSQL = Nothing
            sCondition = Nothing
            _DLMain = Nothing
        End Try
    End Function



















    ''' <summary>
    ''' Fetches User Details based on specific condition
    ''' </summary>
    ''' <param name="dtUserDetails">User Details DataTable</param>
    ''' <param name="sCondition">Condition for fetching data</param>
    ''' <param name="sSort">Sort condition</param>
    ''' <remarks></remarks>
    Public Sub FetchUserDetails(ByRef dtUserDetails As DataTable, ByVal sCondition As String, Optional ByVal sSort As String = "UserName ASC")
        Try
            If IsEmpty(sCondition) Then sCondition = "(1=1)"
            If IsEmpty(sSort) Then sSort = "UserName ASC"
            If IsEmpty(dtUserDetails) Then dtUserDetails = New DataTable
            ExecuteSQL("SELECT * FROM UserDetailsView WHERE " & sCondition & " ORDER BY " & sSort, dtUserDetails, "UserDetails_UserName")
        Finally
            sCondition = Nothing
            sSort = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Fetches User Details based on specific User Name
    ''' </summary>
    ''' <param name="sUserName">User Name</param>
    ''' <param name="dtUserDetails">User Details DataTable</param>
    ''' <param name="sCondition">Additional Condition for fetching data</param>
    ''' <param name="sSort">Sort condition</param>
    ''' <remarks></remarks>
    Public Sub FetchUserDetails(ByVal sUserName As String, ByRef dtUserDetails As DataTable, _
    ByVal sCondition As String, Optional ByVal sSort As String = "UserName ASC")
        Try
            If IsEmpty(sCondition) Then sCondition = "(1=1)"
            If IsEmpty(sSort) Then sSort = "UserName ASC"
            If IsEmpty(dtUserDetails) Then dtUserDetails = New DataTable
            ExecuteSQL("SELECT * FROM UserDetailsView WHERE UserName = '" & sUserName & "' AND " & sCondition & " ORDER BY " & sSort, dtUserDetails, "UserDetails_UserName")
        Finally
            sUserName = Nothing
            sCondition = Nothing
            sSort = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Fetches User Details based on specific PKey of UserMain table
    ''' </summary>
    ''' <param name="pkUserMainPKey">UserMain.PKey</param>
    ''' <param name="dtUserDetails">User Details DataTable</param>
    ''' <param name="sCondition">Additional Condition for fetching data</param>
    ''' <param name="sSort">Sort condition</param>
    ''' <remarks></remarks>
    Public Sub FetchUserDetails(ByVal pkUserMainPKey As Integer, ByRef dtUserDetails As DataTable, _
    ByVal sCondition As String, Optional ByVal sSort As String = "UserName ASC")
        Try
            If IsEmpty(sCondition) Then sCondition = "(1=1)"
            If IsEmpty(sSort) Then sSort = "PKey ASC"
            If IsEmpty(dtUserDetails) Then dtUserDetails = New DataTable
            ExecuteSQL("SELECT * FROM UserDetailsView WHERE PKey = '" & pkUserMainPKey & "' AND " & sCondition & " ORDER BY " & sSort, dtUserDetails, "UserDetails_UserName")
        Finally
            pkUserMainPKey = Nothing
            sCondition = Nothing
            sSort = Nothing
        End Try
    End Sub

#End Region

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="sCSVFullPath"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function ReadWatchlistCSV(ByVal sCSVFullPath As String) As DataTable
        Dim dtCSV As DataTable
        Try
            dtCSV = New DataTable
            If IsFileExists(sCSVFullPath) Then ReadCSV(sCSVFullPath, dtCSV, "Watchlist")
            Return dtCSV
        Finally
            sCSVFullPath = Nothing
            dtCSV = Nothing
        End Try
    End Function

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="sCSVFullPath"></param>
    ''' <param name="dtCSV"></param>
    ''' <remarks></remarks>
    Public Sub ReadWatchlistCSV(ByVal sCSVFullPath As String, ByRef dtCSV As DataTable)
        dtCSV = ReadWatchlistCSV(sCSVFullPath)
    End Sub

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="sCSVFullPath"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ReadFileFromDB(ByVal sCSVFullPath As String) As DataTable

    End Function
End Class
